Oracle count(*)是否走索引 | 您所在的位置:网站首页 › oracle in是否走索引 › Oracle count(*)是否走索引 |
count(*)在平常工作中,使用到的频率很高,是否会走索引,对性能影响不小!但是不是所有的count(*)都能走索引!小记下 create table t3 ( sid number not null primary key, sno number, sname varchar2(10) ) tablespace test; declare maxrecords constant int:=100000; i int :=1; begin for i in 1..maxrecords loop insert into t3 values(i,i,'ocpyang'); end loop; dbms_output.put_line(' 成功录入数据! '); commit; end; / declare maxrecords constant int:=200000; i int :=100001; begin for i in 100001..maxrecords loop insert into t3(sid,sname) values(i,'ocpyang'); end loop; dbms_output.put_line(' 成功录入数据! '); commit; end; / create index index_sno on t3(sno); exec dbms_stats.gather_table_stats('SYS','T3',cascade=>TRUE); *********** 1.count *********** SQL> set autotrace traceonly explain stat; SQL> select count(*) from t3; 执行计划 ---------------------------------------------------------- Plan hash value: 463314188 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T3 | 82 | 2 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - SQL plan baseline "SQL_PLAN_27gnhfjz9qahj14fae16c" used for this statement 统计信息 ---------------------------------------------------------- 55 recursive calls 38 db block gets 521 consistent gets 19 physical reads 14676 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --通过全表扫描实现的. SQL> select count(*) from t1 where sid is not null; 执行计划 ---------------------------------------------------------- Plan hash value: 1551730033 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 68 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FAST FULL SCAN| SYS_C0023596 | 85899 | 1090K| 68 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_4xztry6akgpqqf2d247c8" used for this statement 统计信息 ---------------------------------------------------------- 4 recursive calls 0 db block gets 310 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --通过索引实现的. |
CopyRight 2018-2019 实验室设备网 版权所有 |